
--
-- Drop taMemberCopy and MemberChanges and MemberErrors (if exists)
--
--    This ensures this sql file can always be
--    be executed even if the previous iteration
--    failed.
--
drop table taMemberCopy;
drop table MemberChanges;

--
-- Create taMemberCopy taking a copy of all data
--
--    Taking allows us to work with the copy instead
--    of the actual data, allowing other people to
--    use the database while this script is running.
--
create table taMemberCopy
  as 
    (
      select *
        from taMember
    )
;

--
-- Create taMemberOld as empty table if it does not exist
--
--    This ensures that we can use the same logic
--    to take changes in the following step.
--
create table taMemberOld
  as
    (
      select *
        from taMemberCopy
        where 1=0
    )
;

--
-- Create the MemberChanges Table
--
--    Create a table containing all the changes
--    to taMember, including a new column named
--    operation as either 'added', 'changed' or
--    'deleted' indicating what change happend.
--
create table MemberChanges as
select  'added' as operation
      , m.memberno
      , m.initials
      , m.name
      , m.address
      , m.zipcode
      , m.dateborn
      , m.datejoined
      , m.dateleft
      , m.ownsplanereg
      , m.statusstudent
      , m.statuspilot
      , m.statusascat
      , m.statusfullcat
      , m.sex
      , m.club
  from taMemberCopy m
  where exists (
                  select 'x'
                    from ( select  new.memberno
                              from taMemberCopy new
                          minus
                           select  old.memberno
                              from taMemberOld old
                          ) added
                    where m.memberno = added.memberno
                )
union
select  'changed' as operation
      , memberno
      , initials
      , name
      , address
      , zipcode
      , dateborn
      , datejoined
      , dateleft
      , ownsplanereg
      , statusstudent
      , statuspilot
      , statusascat
      , statusfullcat
      , sex
      , club
  from  (
           select mc.memberno
                , mc.initials
                , mc.name
                , mc.address
                , mc.zipcode
                , mc.dateborn
                , mc.datejoined
                , mc.dateleft
                , mc.ownsplanereg
                , mc.statusstudent
                , mc.statuspilot
                , mc.statusascat
                , mc.statusfullcat
                , mc.sex
                , mc.club
             from taMemberCopy mc
           minus
          select  mo.memberno
                , mo.initials
                , mo.name
                , mo.address
                , mo.zipcode
                , mo.dateborn
                , mo.datejoined
                , mo.dateleft
                , mo.ownsplanereg
                , mo.statusstudent
                , mo.statuspilot
                , mo.statusascat
                , mo.statusfullcat
                , mo.sex
                , mo.club
               from  taMemberOld mo
          ) changed
  where not exists (
                  select 'x'
                    from ( select  new.memberno
                              from taMemberCopy new
                          minus
                           select  old.memberno
                              from taMemberOld old
                          ) added
                    where changed.memberno = added.memberno
                )
union
select  'deleted' as operation
      , m.memberno
      , m.initials
      , m.name
      , m.address
      , m.zipcode
      , m.dateborn
      , m.datejoined
      , m.dateleft
      , m.ownsplanereg
      , m.statusstudent
      , m.statuspilot
      , m.statusascat
      , m.statusfullcat
      , m.sex
      , m.club
  from  taMemberCopy m
  where exists (
                  select 'x'
                    from ( select  old.memberno
                              from taMemberOld old
                          minus
                           select  new.memberno
                              from taMemberCopy new
                          ) deleted
                    where m.memberno = deleted.memberno
                )
;

--
--  Create error table to store any errors that may occur
--
create table MemberErrors
  as
    (
      select 'This column should contain an error message, beware!' as reason
            , m.memberno
            , m.initials
            , m.name
            , m.address
            , m.zipcode
            , m.dateborn
            , m.datejoined
            , m.dateleft
            , m.ownsplanereg
            , m.statusstudent
            , m.statuspilot
            , m.statusascat
            , m.statusfullcat
            , m.sex
            , m.club
        from taMemberCopy m
        where 1 = 0
    )
;

--
--    QUALITY_ASSURE is a function that assures that the row_to_qa
--    is valid.
--
--  This function takes two arguments:
--    - row_to_qa is an "IN" parameter and contains the row that should
--      be quality assured
--    - row_to_add is an "OUT" parameter that will contain the result
--      of the quality assured row, ready to be inserted into a dimension.
--    - RETURNS an error message. If this is null, it means the quality
--      assurance did not find any problems.
--
create or replace function quality_assure (
    row_to_qa in MemberChanges%rowtype
  , row_to_add out Dim_Member%rowtype)
return varchar2
is
  --
  -- Exceptions
  --
  invalid_status exception;
  invalid_club exception;
  invalid_dates exception;
  invalid_glider exception;
  invalid_age exception;
  
  true_status_count number(3,0) := 0;
  club_count number(3,0) := 0;
  glider_count number(3,0) := 0;
  minimum_age constant interval year To month := interval '12-0' year to month;
  rest_of_name varchar2(256) := null;
  name_unit varchar2(64) := null;
  name_split_index number(5,0) := 0;
  rest_of_name_length number(5,0) := 0;
  first_name varchar2(64) := null;
  last_name varchar2(64) := null;
  middle_names varchar2(64) := null;
begin
  --
  -- Check all four status' to ensure ONLY ONE is set to true.
  -- Also set variable for insertion in dimension.
  --
  if row_to_qa.statusstudent = 'y' or row_to_qa.statusstudent = 'Y' then
    true_status_count := true_status_count + 1;
    row_to_add.status := 'Student';
  end if;
  if row_to_qa.statuspilot = 'y' or row_to_qa.statuspilot = 'Y' then
    true_status_count := true_status_count + 1;
    row_to_add.status := 'Pilot';
  end if;
  if row_to_qa.statusascat = 'y' or row_to_qa.statusascat = 'Y' then
    true_status_count := true_status_count + 1;
    row_to_add.status := 'Assistant Category';
  end if;
  if row_to_qa.statusfullcat = 'y' or row_to_qa.statusfullcat = 'Y' then
    true_status_count := true_status_count + 1;
    row_to_add.status := 'Full Category';
  end if;
    if true_status_count > 1 or true_status_count < 1 then
    raise invalid_status;
  end if;
  
  -- 
  -- Check that ensures the glider exists!
  --
  row_to_add.gliderregistration := regexp_replace(row_to_qa.ownsplanereg, '[[:space:]]*',''); 
  if row_to_add.gliderregistration != '' then
  
    --
    -- Make sure it starts with OY
    --
    if row_to_add.gliderregistration not like 'OY%' then
      row_to_add.gliderregistration := 'OY' || row_to_add.gliderregistration;
    end if;
	
    -- 
    -- Check that ensures the glider exists!
    --    (THIS MUST BE EXECUTED AFTER DIM_GLIDER HAS BEEN CREATED)
    --    (taGlider SHOULD BE CHANGED TO DIM_GLIDER)
    --
    select count(*)
      into glider_count
      from taGlider
     where registration = row_to_add.gliderregistration;
    if club_count != 1 then
      raise invalid_glider;
    end if;
  end if;
  
  -- 
  -- Check that ensures the dates are proper
  --    (DateBorn, DateJoirned, DateLeft)
  --
  if (row_to_qa.dateborn + minimum_age) >= row_to_qa.datejoined then
    raise invalid_dates;
  end if;
  if row_to_qa.dateleft is not null and row_to_qa.datejoined > row_to_qa.dateleft then
    raise invalid_dates;
  end if;
  
  -- 
  -- Check that ensures the club exists!
  --    (THIS MUST BE EXECUTED AFTER DIM_CLUB HAS BEEN CREATED)
  --    (taClub SHOULD BE CHANGED TO DIM_CLUB)
  --
  select count(*)
    into club_count
    from taClub
    where mane = row_to_qa.club;
  if club_count != 1 then
    raise invalid_club;
  end if;
  
  --
  -- Check that age is valid
  --
  if trunc(months_between(sysdate, row_to_qa.dateborn) / 12) > 150 then
    raise invalid_age;
  end if;
  
  --
  -- Change age to either 'Male' or 'Female'
  --    This check should not cause a problem since
  --    this column is restricted to only 'm' or 'f'.
  --
  if row_to_qa.sex = 'm' or row_to_qa.sex = 'M' then
    row_to_add.sex := 'Male';
  else
    row_to_add.sex := 'Female';
  end if;
  
  --
  -- Split name into components
  --    This ensures we get our name into components.
  --
  rest_of_name := row_to_qa.name;
  rest_of_name := trim(rest_of_name);
  rest_of_name_length := length(rest_of_name);
  
  while rest_of_name_length > 0 loop
    --
    -- find split index
    --
    name_split_index := instrc(rest_of_name, ' ');
    if name_split_index = 0 then
      name_split_index := length(rest_of_name);
    end if;
	
    --
    -- Find next unit of a name
    --
    name_unit := substrc(rest_of_name, 0, name_split_index);
    name_unit := trim(name_unit);
    
    --
    -- Update rest_of_name and rest_of_name_length
    --
    rest_of_name := substrc(rest_of_name, name_split_index + 1);
    rest_of_name := trim(rest_of_name);
    rest_of_name_length := length(rest_of_name);
    
    --
    -- Update name components
    --
    if first_name is null then
      first_name := name_unit;
    elsif rest_of_name_length is null then
      last_name := name_unit;
    else
      if middle_names is null then
        middle_names := name_unit;
      else
        middle_names := middle_names || ' ' || name_unit;
      end if;
    end if;
  end loop;
  row_to_add.firstname := first_name;
  row_to_add.middlename := middle_names;
  row_to_add.lastname := last_name;
  
  --
  -- All QA complete, current row green-lit.
  -- Set rest of members, and then return null.
  --
  row_to_add.memberid := member_seq.nextval;
  row_to_add.memberno := row_to_qa.memberno;
  row_to_add.initials := row_to_qa.initials;
  row_to_add.fullname := row_to_qa.name;
  row_to_add.address := row_to_qa.address;
  row_to_add.zipcode := row_to_qa.zipcode;
  row_to_add.age := trunc(months_between(sysdate, row_to_qa.dateborn) / 12);
  row_to_add.datejoined := row_to_qa.datejoined;
  row_to_add.dateleft := row_to_qa.dateleft;
  row_to_add.clubname := row_to_qa.club;
  return null;
  
exception
  when invalid_status then
    return 'Invalid status specified';
  when invalid_club then
    return 'Club does not exist';
  when invalid_dates then
    return 'Dates specified are not valid';
  when invalid_glider then
    return 'Glider with specified registration does not exist';
  when invalid_age then
    return 'Too large age';
    
end;
/


--
-- Count member before
--
drop table Dim_MemberCountBefore;
create table Dim_MemberCountBefore
  as
  select tab1.ActiveCounter, tab2.InactiveCounter
    from
        (
          select count(*) as ActiveCounter
            from Dim_Member
            where ValidTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
        ) tab1
    cross join
        (
          select count(*) as InactiveCounter
            from Dim_Member
            where ValidTo != to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
        ) tab2
;

--
-- Final PL/SQL block to add/update rows in Dim_Member
--
declare
  --
  -- Cursors
  --
  cursor member_additions is
    select *
    from MemberChanges
    where operation = 'added';
    
  cursor member_changes is
    select *
    from MemberChanges
    where operation = 'changed';
    
  cursor member_deletions is
    select *
    from MemberChanges
    where operation = 'deleted';
begin
  -- 
  -- Go through all additions
  --
  for row_to_qa in member_additions loop
    declare
      error_message varchar2(256) := null;
      row_to_add Dim_Member%rowtype;
    begin
      --
      -- Ensure quality
      --
      error_message := quality_assure(row_to_qa, row_to_add);
      
      --
      -- if error_message is null, it means the quality is okay
      --
      if error_message is null then
        --
        -- If no errors happend. Just add the row
        --
        insert into Dim_Member
          (
              MemberId
            , MemberNo
            , Sex
            , Initials
            , FirstName
            , MiddleName
            , LastName
            , FullName
            , Address
            , ZipCode
            , Age
            , DateJoined
            , DateLeft
            , GliderRegistration
            , ClubName
            , Status
            , ValidFrom
            , ValidTo
          )
        values
          (
              row_to_add.memberid
            , row_to_add.memberno
            , row_to_add.sex
            , row_to_add.initials
            , row_to_add.firstname
            , row_to_add.middlename
            , row_to_add.lastname
            , row_to_add.fullname
            , row_to_add.address
            , row_to_add.zipcode
            , row_to_add.age
            , row_to_add.datejoined
            , row_to_add.dateleft
            , row_to_add.gliderregistration
            , row_to_add.clubname
            , row_to_add.status
            , trunc(sysdate,'DDD')
            , to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
          );
      else
        --
        -- On errors, add to MemberErrors table
        --
        insert into MemberErrors 
          (
              reason
            , memberno
            , initials
            , name
            , address
            , zipcode
            , dateborn
            , datejoined
            , dateleft
            , ownsplanereg
            , statusstudent
            , statuspilot
            , statusascat
            , statusfullcat
            , sex
            , club
          ) 
        values 
          (
              error_message
            , row_to_qa.memberno
            , row_to_qa.initials
            , row_to_qa.name
            , row_to_qa.address
            , row_to_qa.zipcode
            , row_to_qa.dateborn
            , row_to_qa.datejoined
            , row_to_qa.dateleft
            , row_to_qa.ownsplanereg
            , row_to_qa.statusstudent
            , row_to_qa.statuspilot
            , row_to_qa.statusascat
            , row_to_qa.statusfullcat
            , row_to_qa.sex
            , row_to_qa.club
          );
      end if;
    end;
  end loop;
  
  -- 
  -- Go through all updates
  --
  for row_to_qa in member_changes loop
    declare
      error_message varchar2(256) := null;
      row_to_change Dim_Member%rowtype;
    begin
      --
      -- Ensure quality
      --
      error_message := quality_assure(row_to_qa, row_to_change);
      
      --
      -- if error_message is null, it means the quality is okay
      --
      if error_message is null then
        --
        -- If no errors happend, first update the previous row,
        -- that now must be invalidated
        --
        update Dim_Member
          set ValidTo = trunc(sysdate,'DDD') - to_dsInterval('000 00:00:01')
          where memberno = row_to_change.memberno
            and ValidTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS');
        
        --
        -- Insert new row
        --
        insert into Dim_Member
          (
              MemberId
            , MemberNo
            , Sex
            , Initials
            , FirstName
            , MiddleName
            , LastName
            , FullName
            , Address
            , ZipCode
            , Age
            , DateJoined
            , DateLeft
            , GliderRegistration
            , ClubName
            , Status
            , ValidFrom
            , ValidTo
          )
        values
          (
              row_to_change.memberid
            , row_to_change.memberno
            , row_to_change.sex
            , row_to_change.initials
            , row_to_change.firstname
            , row_to_change.middlename
            , row_to_change.lastname
            , row_to_change.fullname
            , row_to_change.address
            , row_to_change.zipcode
            , row_to_change.age
            , row_to_change.datejoined
            , row_to_change.dateleft
            , row_to_change.gliderregistration
            , row_to_change.clubname
            , row_to_change.status
            , trunc(sysdate,'DDD')
            , to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
          );
      else
        --
        -- On errors, add to MemberErrors table
        --
        insert into MemberErrors 
          (
              reason
            , memberno
            , initials
            , name
            , address
            , zipcode
            , dateborn
            , datejoined
            , dateleft
            , ownsplanereg
            , statusstudent
            , statuspilot
            , statusascat
            , statusfullcat
            , sex
            , club
          ) 
        values 
          (
              error_message
            , row_to_qa.memberno
            , row_to_qa.initials
            , row_to_qa.name
            , row_to_qa.address
            , row_to_qa.zipcode
            , row_to_qa.dateborn
            , row_to_qa.datejoined
            , row_to_qa.dateleft
            , row_to_qa.ownsplanereg
            , row_to_qa.statusstudent
            , row_to_qa.statuspilot
            , row_to_qa.statusascat
            , row_to_qa.statusfullcat
            , row_to_qa.sex
            , row_to_qa.club
          );
      end if;
    end;
  end loop;
  
  -- 
  -- Go through all deletions
  --
  for row_to_delete in member_deletions loop
    begin
      -- 
      -- Just update old row so it is invalidated
      --
      update Dim_Member
        set ValidTo = trunc(sysdate,'DDD') - to_dsInterval('000 00:00:01')
        where memberno = row_to_delete.memberno
          and ValidTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS');
    end;
  end loop;
  
end;
/

--
-- Count Dim_Member after script executed
--
drop table Dim_MemberCountAfter;
create table Dim_MemberCountAfter
  as
  select tab1.ActiveCounter, tab2.InactiveCounter
    from
        (
          select count(*) as ActiveCounter
            from Dim_Member
            where ValidTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
        ) tab1
    cross join
        (
          select count(*) as InactiveCounter
            from Dim_Member
            where ValidTo != to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
        ) tab2
;

--
-- At this point we know the error counts and Dim_Member counts.
-- We now want to make sure that these counts make sense in comparison
-- to eachother!
--
-- [MemberAdded]
select count(*)
  from MemberChanges
  where operation = 'added';

-- [MemberRemoved]
select count(*)
  from MemberChanges
  where operation = 'removed';

-- [ActiveCountBefore]
select ActiveCounter
  from Dim_MemberCountBefore;
  
-- [InactiveCountBefore]
select InactiveCounter
  from Dim_MemberCountBefore;

-- [ActiveCountAfter]
select ActiveCounter
  from Dim_MemberCountAfter;
  
-- [InactiveCountAfter]
select InactiveCounter
  from Dim_MemberCountAfter;
  
--
-- The following must be true:
--    [ActiveCountAfter] - [ActiveCountBefore] <= [MemberAdded]
--    [InactiveCountAfter] - [InactiveCountBefore] <= [MemberRemoved]
--
-- The members added must be greater or equal because some may
-- have been added to the errors table.
--
-- The members removed must also be greater or equal before changes
-- may also increase the inactive count
--

--
-- Final step is to create taMemberOld table. At this point,
-- it already exists, so we will just drop it, and recreate it
-- while filling it with all the rows of taMemberCopy, so it is
-- fully updated with the current state of the operational database.
--
drop table taMemberOld;
create table taMemberOld
  as
    (
      select *
        from taMemberCopy
    )
;

--
-- Drop taMemberCopy, since it is not needed anymore
--
drop table taMemberCopy;

--
-- CHANGE TO COMMIT!
--
commit;
